﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'proc_cm_SaveMember')
BEGIN
    PRINT 'Dropping Procedure proc_cm_SaveMember'
    DROP  Procedure  proc_cm_SaveMember
END
GO

PRINT 'Creating Procedure proc_cm_SaveMember'
GO

CREATE PROCEDURE [dbo].[proc_cm_SaveMember]
	@pMemberId int = null
	, @pFirstnameTxt nvarchar(100) = null
	, @pLastnameTxt nvarchar(100) = null
	, @pKoreanNm nvarchar(10) = null
	, @pEmailaddrTxt nvarchar(256) = null
	, @pHomeAddressTxt nvarchar(255) = null
	, @pHomeAddress2Txt nvarchar(255) = null
	, @pHomeCityTxt nvarchar(255) = null
	, @pHomeStateCd varchar(2) = null
	, @pHomeZipCd varchar(10) = null
	, @pHomePhonenumTxt varchar(25) = null
	, @pCellPhonenumTxt varchar(25) = null
	, @pBirthdate datetime = null
	, @pIconFilenameTxt nvarchar(512) = null
	, @pMemberSinceDttm datetime = null
	, @pLastloginDttm datetime = null
	, @pConfirmationGuid uniqueidentifier = null
	, @pUserId uniqueidentifier
	, @pOutMemberId int OUTPUT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

	IF @pMemberId IS NOT NULL
	BEGIN
		UPDATE
			[dbo].[tbl_member]
		SET 
			[UserId] = @pUserId
			,[firstname_txt] = @pFirstnameTxt
			,[lastname_txt] = @pLastnameTxt
			,[korean_nm] = @pKoreanNm
			,[address_txt] = @pHomeAddressTxt
			,[address2_txt] = @pHomeAddress2Txt
			,[city_txt] = @pHomeCityTxt
			,[state_cd] = @pHomeStateCd
			,[zip_cd] = @pHomeZipCd
			,[homephone_txt] = @pHomePhonenumTxt
			,[cellphone_txt] = @pCellPhonenumTxt
			,[birthdate_dt] = @pBirthdate
			,[icon_filename_txt] = @pIconFilenameTxt
			,[member_since_dt] = @pMemberSinceDttm
			,[last_login_dttm] = @pLastloginDttm
			,[confirmation_guid] = @pConfirmationGuid
		WHERE
			[member_id] = @pMemberId

		UPDATE [dbo].[aspnet_Membership]
		SET [Email] = @pEmailaddrTxt
		WHERE [UserId] = @pUserId

		SET @pOutMemberId = @pMemberId
	END
	ELSE
	BEGIN
		INSERT INTO [AllNationsDb].[dbo].[tbl_member]
		(
			[UserId]
			,[firstname_txt]
			,[lastname_txt]
			,[korean_nm]
			,[address_txt]
			,[address2_txt]
			,[city_txt]
			,[state_cd]
			,[zip_cd]
			,[country_nm]
			,[homephone_txt]
			,[cellphone_txt]
			,[birthdate_dt]
			,[icon_filename_txt]
			,[member_since_dt]
			,[last_login_dttm]
			,[confirmation_guid]
			,[created_dttm]
		)
		VALUES
		(
			@pUserId
			,@pFirstnameTxt
			,@pLastnameTxt
			,@pKoreanNm
			,@pHomeAddressTxt
			,@pHomeAddress2Txt
			,@pHomeCityTxt
			,@pHomeStateCd
			,@pHomeZipCd
			,NULL -- country is not implemented yet
			,@pHomePhonenumTxt
			,@pCellPhonenumTxt
			,@pBirthdate
			,@pIconFilenameTxt
			,@pMemberSinceDttm
			,@pLastloginDttm
			,@pConfirmationGuid
			,GETDATE()
		)

		UPDATE [dbo].[aspnet_Membership]
		SET [Email] = @pEmailaddrTxt
		WHERE [UserId] = @pUserId

		SET @pOutMemberId = SCOPE_IDENTITY()
	END
END
GO

GRANT EXEC ON dbo.proc_cm_SaveMember TO PUBLIC
GO
